package com.shycloud.mido.organ.test;

import com.shycloud.mido.organ.api.entity.MemberActivateEntity;
import com.shycloud.mido.organ.test.util.DBUtil;

import java.sql.*;
import java.text.ParseException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.*;

/**
 * @author Xiaoxing Yu
 * @date 2020/5/29
 **/
public class ActivationDataTransfer {

	public static void main(String[] args) throws Exception {
		ActivationDataTransfer.transfer();
	}

	/**
	 * 转移org_member_activated数据到新表
	 */
	public static void transfer() throws Exception {
		//先查出来老表的所有数据
		List<Map> oldList = getAllInfo(false);
		List<Map> newList = getAllInfo(false);

//		Set<String> oldIdSet = new HashSet();
		Set<String> newIdSet = new HashSet();

//		for (int i = 0; i < oldList.size(); i++) {
//			oldIdSet.add((String)oldList.get(i).get("id"));
//		}
		for (int i = 0; i < newIdSet.size(); i++) {
			newIdSet.add((String) newList.get(i).get("id"));
		}

		for (int i = 0; i < oldList.size(); i++) {
			Map oldMap = oldList.get(i);
			if (newIdSet.contains((String) oldMap.get("id"))) {
				//跳过
				continue;
			} else {
				MemberActivateEntity entity = new MemberActivateEntity();
				entity.setActvCode((String) oldMap.get("id"));
				entity.setUdid((String) oldMap.get("udid"));
				entity.setMemberId((String) oldMap.get("member_id"));
				entity.setOrgId((String) oldMap.get("org_id"));
//				entity.setOptrId((String) oldMap.get("optr_id"));
				entity.setOptrId((Integer) oldMap.get("optr_id"));
				entity.setUdid((String) oldMap.get("udid"));
				entity.setType((String) oldMap.get("type"));

				DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
				try {
					entity.setStartTime(LocalDateTime.parse(((String) oldMap.get("update_time")), df));
				} catch (DateTimeParseException e) {
					entity.setStartTime(LocalDateTime.parse(("2000-01-01 00:00:00"), df));
				}

				entity.setDuration(Integer.valueOf((String) oldMap.get("use_time")));
				entity.setStatus((String) oldMap.get("status"));
				//entity.setCreateTime(StringoldMap.get("update_time"));
				entity.setUpdatedBy((String) oldMap.get("Chris Yu"));

				newIdSet.add((String) oldMap.get("id"));
				insertActivation(entity);
				System.out.println("inserting no." + i + "th record with memberId " +
						entity.getMemberId() + " and code " + (String) oldMap.get("id") + "......");
			}
		}
	}

	public static void insertActivation(MemberActivateEntity entity) throws ParseException {
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			conn = DBUtil.getConn("mido_member");
			String sql = "insert into sm_member_activate (actv_code, udid, member_id, org_id," +
					"optr_id, ref_code, type, special_type, start_time, duration, status, extension," +
					"comment, create_time, update_time, updated_by) values " +
					"(?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?)";
			pstmt = DBUtil.getPStmt(conn, sql);
			pstmt.setString(1, entity.getActvCode());
			pstmt.setString(2, entity.getUdid());
			pstmt.setString(3, entity.getMemberId());
			pstmt.setString(4, entity.getOrgId());
//			pstmt.setString(5, entity.getOptrId());
			pstmt.setInt(5, entity.getOptrId());
			pstmt.setString(6, entity.getRefCode());
			pstmt.setString(7, entity.getType());
			pstmt.setString(8, String.valueOf(entity.getSpecialType()));
			pstmt.setString(9, entity.getStartTime().toString());
			pstmt.setString(10, String.valueOf(entity.getDuration()));
			pstmt.setString(11, entity.getStatus());
			pstmt.setString(12, entity.getExtension());
			pstmt.setString(13, entity.getComment());
			//pstmt.setString(14, entity.getCreateTime());
			pstmt.setString(15, entity.getUpdateTime().toString());
			pstmt.setString(16, entity.getUpdatedBy());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeStmt(pstmt);
			DBUtil.closeConn(conn);
		}
	}

	public static List<Map> getAllInfo(boolean isNew) throws ParseException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			return isNew ? ActivationDataTransfer.getNew(conn, pstmt, rs) : ActivationDataTransfer.getOld(conn, pstmt, rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeStmt(pstmt);
			DBUtil.closeConn(conn);
		}

		return null;
	}

	private static List<Map> getOld(Connection conn, PreparedStatement pstmt, ResultSet rs) throws Exception {
		conn = DBUtil.getConn("mido_member");
		String sql = "select * from org_member_activated";
		pstmt = DBUtil.getPStmt(conn, sql);
		rs = pstmt.executeQuery();
		List<Map> rsList = new ArrayList<>();

		while (rs.next()) {
			String id = rs.getString("id");
			String udid = rs.getString("udid");
			String memberId = rs.getString("member_id");
			String type = rs.getString("type");
			String useTime = rs.getString("use_time");
			String updateTime = rs.getString("update_time");
			int orgId = rs.getInt("org_id");
			int optrId = rs.getInt("optr_id");
			String status = rs.getString("status");

//			System.out.println("id：" + id + ", "+ "udid：" + udid + ", " +
//					"" + "memberId：" + memberId + ", " + "type：" + type + ", " + "useTime：" + useTime + ", " +
//					"updateTime：" + updateTime + ", " + "orgId：" + orgId + ", " +
//					"optrId：" + optrId + ", " + "status：" + status);

			Map rsMap = new HashMap();
			rsMap.put("id", rs.getString("id"));
			rsMap.put("udid", rs.getString("udid"));
			rsMap.put("member_id", rs.getString("member_id"));
			rsMap.put("type", rs.getString("type"));
			rsMap.put("use_time", rs.getString("use_time"));
			rsMap.put("update_time", rs.getString("update_time"));
			rsMap.put("org_id", rs.getString("org_id"));
			rsMap.put("optr_id", rs.getString("optr_id"));
			rsMap.put("status", rs.getString("status"));
			rsList.add(rsMap);
		}

		return rsList;
	}

	private static List<Map> getNew(Connection conn, PreparedStatement pstmt, ResultSet rs) throws Exception {
		conn = DBUtil.getConn("mido_member");
		String sql = "select * from sm_member_activate";
		pstmt = DBUtil.getPStmt(conn, sql);
		rs = pstmt.executeQuery();
		List<Map> rsList = new ArrayList<>();

		while (rs.next()) {
			String id = rs.getString("id");
			String actvCode = rs.getString("actv_code");
			String udid = rs.getString("udid");
			String memberId = rs.getString("member_id");
			int orgId = rs.getInt("org_id");
			int optrId = rs.getInt("optr_id");
			String refCode = rs.getString("ref_code");
			String type = rs.getString("type");
			String specialType = rs.getString("special_type");
			String startTime = rs.getString("start_time");
			int duration = rs.getInt("duration");
			String status = rs.getString("status");
			String extension = rs.getString("extension");
			String comment = rs.getString("comment");
			String createTime = rs.getString("create_time");
			String updateTime = rs.getString("update_time");
			String updatedBy = rs.getString("updated_by");

//			System.out.println("id："+id +", "+ "actvCode："+actvCode +", " +
//					""+ "udid："+udid+", "+ "memberId："+memberId+", "+ "orgId："+orgId +", " +
//					"optrId："+optrId +", "+ "refCode："+refCode);

			Map rsMap = new HashMap();
			rsMap.put("id", rs.getString("id"));
			rsMap.put("actv_code", rs.getString("actv_code"));
			rsMap.put("udid", rs.getString("udid"));
			rsMap.put("member_id", rs.getString("member_id"));
			rsMap.put("org_id", rs.getString("org_id"));
			rsMap.put("optr_id", rs.getString("optr_id"));
			rsMap.put("ref_code", rs.getString("ref_code"));
			rsMap.put("type", rs.getString("type"));
			rsMap.put("special_type", rs.getString("special_type"));
			rsMap.put("start_time", rs.getString("start_time"));
			rsMap.put("duration", rs.getString("duration"));
			rsMap.put("status", rs.getString("status"));
			rsMap.put("extension", rs.getString("extension"));
			rsMap.put("comment", rs.getString("comment"));
			rsMap.put("create_time", rs.getString("create_time"));
			rsMap.put("update_time", rs.getString("update_time"));
			rsMap.put("updated_by", rs.getString("updated_by"));
			rsList.add(rsMap);
		}

		return rsList;
	}

}
